﻿using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.Configuration;
using System.Collections.Generic;


/// <summary>
/// public class have public functions and methods
/// </summary>
public class ToolsDT
{
    public static SqlConnection getConnect()
    {
        string strcon = ConfigurationManager.ConnectionStrings["connection"].ConnectionString; 
        SqlConnection cn= new SqlConnection (strcon);
        cn.Open();
        return cn;
        
    }
    
    // login
    public static Boolean doLogin(string uid, string pwd)
    {
        int c = 0;
        string sql = "SELECT count(*) FROM [User] WHERE U_username='" + uid + "'and U_password='" + pwd + "'";
        SqlConnection cn = new SqlConnection();
        SqlCommand cmd =new SqlCommand();
        try
        {
            cn = getConnect();
            cmd.CommandText = sql;
            cmd.Connection = cn;
            c = (int)cmd.ExecuteScalar();
            cn.Close();
            cmd.Dispose();
            
        }

        catch
        {
            return false;
            
        }
        finally
        {
            cn.Close();
            cmd.Dispose();           
        }

        return (c != 0);
    }
   

    // check user exist
    public static Boolean exitsUser(string uid)
    {
        SqlConnection cn = new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        string sql = "Select count(*) from [User] where U_username='" + uid + "'";
        int c = 0;
        try
        {            
            cn = getConnect();
            cmd.CommandText = sql;
            cmd.Connection = cn;
            c = (int) cmd.ExecuteScalar();
            return (c != 0);
        }
        catch
        {
            return true;
        }
        finally
        {
            cn.Close();
            cmd.Dispose();
        }
    }


  

    
    public static string toStringOK(string st)
    {
        string str = st.Replace("'","''");
        return str;
    }

    //check is number
    public static bool isNumber(string st)
    {
        StringBuilder stb = new StringBuilder(st);
        for (int i = 0; i < stb.Length; i++)
        { 
            if (((int)stb[i] < 48) || ((int) stb[i] > 57))
            {
                return false;
            }
        }
        return true;
    }

    // check email exist
    public static Boolean exitsEmail(string mail)
    {
        SqlConnection cn = new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        string sql = "Select count(*) from [User] where U_email='" + mail + "'";
        int c = 0;
        try
        {
            cn = getConnect();
            cmd.CommandText = sql;
            cmd.Connection = cn;
            c = (int)cmd.ExecuteScalar();
            return (c != 0);
        }
        catch
        {
            return true;
        }
        finally
        {
            cn.Close();
            cmd.Dispose();
        }
    }

    //check phone number
    public static Boolean exitsPhone(string number)
    {
        SqlConnection cn = new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        string sql = "Select count(*) from [User] where U_mobile='" + number + "'";
        int c = 0;
        try
        {
            cn = getConnect();
            cmd.CommandText = sql;
            cmd.Connection = cn;
            c = (int)cmd.ExecuteScalar();
            return (c != 0);
        }
        catch
        {
            return true;
        }
        finally
        {
            cn.Close();
            cmd.Dispose();
        }
    }




    // get UserID by Username
    public static int getUserID(string uid)
    {
        string sql = "Select UserID from [User] where U_username='" + uid + "'";
        int id =-1;
        try
        {
            SqlConnection cn = getConnect();
            SqlCommand cmd = new SqlCommand(sql, cn);
            id = (int)cmd.ExecuteScalar();
            cn.Close();
            cmd.Dispose();
        }
        catch { };

        return id;

    }

    // get Friend by UserID
    public static DataTable getFriendbyID(int userid)
    {
            SqlConnection cn = getConnect();
            SqlCommand cmd = new SqlCommand("sp_GetFriendByUserID", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@userid", SqlDbType.Int);
            cmd.Parameters["@userid"].Value = userid;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cn.Close();
            cmd.Dispose();
            return dt;
    }

    // send message to friend
    public static void sendMessageToFriend(int userid, int friendid, string detail,bool status)
    {
        try
        {
            SqlConnection cn = getConnect();
            SqlCommand cmd = new SqlCommand("sp_SendMessageToFriend", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@userid", SqlDbType.Int);
            cmd.Parameters.Add("@friendid", SqlDbType.Int);
            cmd.Parameters.Add("@detail", SqlDbType.VarChar, 200);
            cmd.Parameters.Add("@status", SqlDbType.Bit);
            cmd.Parameters["@userid"].Value = userid;
            cmd.Parameters["@friendid"].Value = friendid;
            cmd.Parameters["@detail"].Value = detail;
            cmd.Parameters["@status"].Value = status;
            cmd.ExecuteNonQuery();
            cn.Close();
            cmd.Dispose();
        }
        catch
        { 
        }
    }

    // View message pending
    public static DataTable ViewMessagePending(int userid)
    {
        SqlConnection cn = getConnect();
        SqlCommand cmd = new SqlCommand("sp_ShowMessagePenDing", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@userid", SqlDbType.Int);
        cmd.Parameters["@userid"].Value = userid;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        cn.Close();
        cmd.Dispose();
        return dt;
    }

    // view all message
    public static DataTable ViewMessage(int userid)
    {
        SqlConnection cn = getConnect();
        SqlCommand cmd = new SqlCommand("sp_ShowMessage", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@userid", SqlDbType.Int);
        cmd.Parameters["@userid"].Value = userid;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        cn.Close();
        cmd.Dispose();
        return dt;
    }

    // show Invitation pending
    public static DataTable ShowInvitationPenDing(int userid)
    {
        SqlConnection cn = getConnect();
        SqlCommand cmd = new SqlCommand("sp_ShowInvitationPenDing", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@userid", SqlDbType.Int);
        cmd.Parameters["@userid"].Value = userid;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        cn.Close();
        cmd.Dispose();
        return dt;
    }

    // show Invitation
    public static DataTable ShowInvitation(int userid)
    {
        SqlConnection cn = getConnect();
        SqlCommand cmd = new SqlCommand("sp_ShowInvitation", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@userid", SqlDbType.Int);
        cmd.Parameters["@userid"].Value = userid;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);   
        cn.Close();
        cmd.Dispose();
        return dt;
    }

    // send Invite
    public static void sendInvite(int userid,int toUser,string status)
    {
        try
        {
            SqlConnection cn = getConnect();
            SqlCommand cmd = new SqlCommand("sp_SendInvite", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@userid", SqlDbType.Int);
            cmd.Parameters.Add("@toUser", SqlDbType.Int);
            cmd.Parameters.Add("@status", SqlDbType.VarChar, 20);
            cmd.Parameters["@userid"].Value = userid;
            cmd.Parameters["@toUser"].Value = toUser;
            cmd.Parameters["@status"].Value = status;
            cmd.ExecuteNonQuery();
            cn.Close();
            cmd.Dispose();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: " + ex);
        }
    }

    // check length of Number mobile
    public static Boolean checkTel(string strTel)
    {
        if (strTel.Length < 10 || strTel.Length > 10)
        {
            return false;
        }
        else
        {
            StringBuilder stb = new StringBuilder(strTel);
            for (int i = 0; i < stb.Length; i++)
            {
                if (stb[i] < '0' || stb[i] > '9')
                {
                    return false;
                }
            }
        }
        return true;
    }


    // get all of user not friend
    public static DataTable GetDataTable(int userid)
    {
        SqlConnection cn = getConnect();
        SqlDataAdapter adap = new SqlDataAdapter("select * from [User] where UserID<>'" + userid + "' and UserID not in (select friendAccountID from friend where UserID='"+userid+"')", cn);
        DataTable dt = new DataTable();
        adap.Fill(dt);
        cn.Close();
        return dt;
    }

    // get all of friend
    public static DataTable GetFriend(int userid)
    {
        SqlConnection cn = getConnect();
        SqlDataAdapter adap = new SqlDataAdapter("select * from [User] where UserID in (select friendAccountID from friend where UserID='" + userid + "')", cn);
        DataTable dt = new DataTable();
        adap.Fill(dt);
        cn.Close();
        return dt;
    }

    // accept Invite
    public static void acceptInvite(int id, int userid, int friendid)
    {
        try
        {
            SqlConnection cn = getConnect();
            SqlCommand cmd = new SqlCommand("sp_AcceptInvite", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            cmd.Parameters.Add("@userid", SqlDbType.Int).Value = friendid;
            cmd.Parameters.Add("@friendid", SqlDbType.Int).Value = userid;
            cmd.ExecuteNonQuery();
            cn.Close();
            cmd.Dispose();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: " + ex);
        }
      
    }
    public static void UpdatePassword(string user, string pass)
    {      
        try
        {
            SqlConnection cn = getConnect();
            SqlCommand cmd = new SqlCommand("sp_UpdatePassword", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@username", SqlDbType.VarChar, 50).Value = user;
            cmd.Parameters.Add("@pass", SqlDbType.VarChar, 50).Value = pass;
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            cn.Close();
        }
        catch (SqlException ex)
        {
            Console.WriteLine("Error: " + ex);
        }
        finally
        {
            
        }
    }
}
